<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Member_model extends CI_Model {

	function __construct() {
		parent::__construct();
	}

	/**
	 * 사용자 리스트 가져오기
	 */
	function select_member_list($user_id, $user_name, $nickname, $start_dt, $end_dt, $page_no, $page_size) {
		log_message('debug',"#### Member_model::select_member_list($user_id, $user_name, $nickname, $start_dt, $end_dt, $page_no, $page_size)");

		$start_row = ($page_no-1) * $page_size;

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       M.* ";
		$sql .= PHP_EOL."     , (SELECT class_name FROM tb_member_class_code WHERE class_cd = (IF(PM.package_cd > 1,'PRE','GEN')) ) class_name ";
		$sql .= PHP_EOL."     , P.point ";
		$sql .= PHP_EOL."     , P.cash ";
		$sql .= PHP_EOL."     , IFNULL(PM.package_cd, '1') package_cd ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = IFNULL(PM.package_cd, '1')) package_name ";
		$sql .= PHP_EOL."     , PM.start_dt ";
		$sql .= PHP_EOL."     , PM.end_dt ";
		$sql .= PHP_EOL." FROM  tb_member M ";
		$sql .= PHP_EOL." INNER JOIN tb_point P ON M.user_id = P.user_id ";
		$sql .= PHP_EOL." LEFT OUTER JOIN (SELECT * FROM tb_member_package WHERE DATE_FORMAT(CURDATE(),'%Y%m%d') BETWEEN start_dt AND end_dt) PM ON M.user_id = PM.user_id ";
		$sql .= PHP_EOL." WHERE M.user_id <> 'guests' ";
		if (!empty($user_name)) {
			$sql .= PHP_EOL." AND   M.user_name LIKE '%$user_name%' ";
		}
		if (!empty($nickname)) {
			$sql .= PHP_EOL." AND   M.nickname LIKE '%$nickname%' ";
		}
		if (!empty($user_id)) {
			$sql .= PHP_EOL." AND   M.user_id = '$user_id' ";
		}
		if (!empty($start_dt) && !empty($end_dt)) {
			$sql .= PHP_EOL." AND   SUBSTRING(M.create_dt,1,10) BETWEEN '$start_dt' AND '$end_dt' ";
		}
		$sql .= PHP_EOL." ORDER BY M.idx DESC ";
		$sql .= PHP_EOL." LIMIT $start_row, $page_size ";
				
		$query = $this->db->query($sql) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

		/**
	 * 총 내역 수
	 */
	function get_member_total_count($user_id, $user_name, $nickname, $start_dt, $end_dt) {
		log_message('debug',"#### Member_model::get_member_total_count($user_id, $user_name, $nickname, $start_dt, $end_dt)");

		if (!empty($user_name)) {
			$this->db->like('user_name', $user_name);
		}
		if (!empty($nickname)) {
			$this->db->like('nickname', $nickname);
		}
		if (!empty($user_id)) {
			$this->db->where('user_id', $user_id);
		}
		if (!empty($start_dt) && !empty($end_dt)) {
			$this->db->where("SUBSTRING(create_dt,1,10) BETWEEN", " '$start_dt' AND '$end_dt' ", FALSE);
		}
		$this->db->where("user_id <>", 'guests');
		$this->db->from('tb_member');
		$result = $this->db->count_all_results();

		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}
	
	/**
	 * 개별이용권 소유 리스트 가져오기
	 */
	function select_ticket_list($user_id, $used_yn = NULL) {
		log_message('debug',"#### Member_model::select_ticket_list($user_id, $used_yn)");

		$this->db->select('T.user_id, T.service_group_id, (SELECT service_name FROM tb_service WHERE service_id = T.service_id) service_name, T.used_yn, T.start_dt, T.end_dt, T.create_dt, T.update_dt');
		$this->db->from('tb_ticket T');
		$this->db->where('T.user_id',$user_id);
		if (!empty($used_yn)) {
			$this->db->where('T.used_yn',$used_yn);
		}
		$this->db->order_by('T.used_yn','asc');
		$this->db->order_by('T.idx','desc');
		
		$query = $this->db->get() or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

		
	/**
	 * VIP회원 리스트
	 */
	function select_member_vip_list() {
		log_message('debug',"#### Member_model::select_member_vip_list()");

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       M.* ";
		$sql .= PHP_EOL."     , P.point ";
		$sql .= PHP_EOL."     , P.cash ";
		$sql .= PHP_EOL."     , PM.package_cd ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = IFNULL(PM.package_cd, '1')) package_name ";
		$sql .= PHP_EOL."     , PM.start_dt ";
		$sql .= PHP_EOL."     , PM.end_dt ";
		$sql .= PHP_EOL." FROM  tb_member M ";
		$sql .= PHP_EOL." INNER JOIN tb_point P ON M.user_id = P.user_id ";
		$sql .= PHP_EOL." INNER JOIN (SELECT * FROM tb_member_package WHERE DATE_FORMAT(CURDATE(),'%Y%m%d') BETWEEN start_dt AND end_dt) PM ON M.user_id = PM.user_id ";
		$sql .= PHP_EOL." WHERE M.user_id <> 'guests' ";
		$sql .= PHP_EOL." AND   (PM.giver_id IS NULL OR PM.giver_id = '') ";
		$sql .= PHP_EOL." ORDER BY M.idx DESC ";
		$query = $this->db->query($sql) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	function get_member_package_with_coupon_list($user_id) {
		log_message('debug',"#### Member_model::get_member_package_with_coupon_list($user_id)");
		
		$member_package = $this->get_member_package($user_id);
		
		$member_package['coupon_list'] = $this->select_member_coupon_list($member_package['idx']);
		
		$this->logsql->log_result($member_package);
		
		return $member_package;
	}


	/**
	 * 회원의 이용중인 상품 정보
	 */
	function get_member_package($user_id) {
		log_message('debug',"#### Member_model::get_member_package($user_id)");

		$sql  = PHP_EOL." SELECT P.* ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = P.package_cd ) package_name ";
		$sql .= PHP_EOL." FROM  tb_member_package P ";
		$sql .= PHP_EOL." WHERE DATE_FORMAT(CURDATE(),'%Y%m%d') BETWEEN P.start_dt AND P.end_dt ";
		$sql .= PHP_EOL." AND   P.user_id = ? ";
        $query = $this->db->query($sql, array($user_id)) or $this->logsql->log_sql('error');
		$result = $query->row_array();

		$this->logsql->log_sql();
		//$this->logsql->log_result($result);

		return $result;
	}
		
	/**
	 * 이용중인 패키지의 잔여 쿠폰 리스트
	 */
	function select_member_coupon_list($idx) {
		log_message('debug',"#### Member_model::select_member_coupon_list($idx)");

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       C.idx ";
		$sql .= PHP_EOL."     , C.service_group_id ";
		$sql .= PHP_EOL."     , (SELECT service_group_name FROM tb_service_group WHERE service_group_id = C.service_group_id) service_group_name ";
		$sql .= PHP_EOL."     , C.qty ";
		$sql .= PHP_EOL."     , ( SELECT qty  ";
		$sql .= PHP_EOL."         FROM  tb_package_coupon ";
		$sql .= PHP_EOL."         WHERE service_group_id = C.service_group_id  ";
		$sql .= PHP_EOL."         AND   package_cd = ( ";
		$sql .= PHP_EOL."                 SELECT package_cd  ";
		$sql .= PHP_EOL."                 FROM tb_member_package  ";
		$sql .= PHP_EOL."                 WHERE idx = C.idx) ";
		$sql .= PHP_EOL."         ) coupon_qty ";
		$sql .= PHP_EOL." FROM  tb_member_package_coupon C ";
		$sql .= PHP_EOL." WHERE idx =  ? ";
		$query = $this->db->query($sql, array($idx)) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		//$this->logsql->log_result($result);

		return $result;
	}
	
	/**
	 * VIP상품 업그레이드
	 */
	function upgrade_member_package($idx) {
		log_message('debug',"#### Member_model::select_member_coupon_list($idx)");
		
		$this->db->trans_start();
		
		// 잔여 쿠폰 조회
		$member_coupon = $this->select_member_coupon_list($idx);
		
		// 업그레이드 할 쿠폰 조회
		$current_package_coupon = $this->select_package_coupon_list('4');
		$upgrade_package_coupon = $this->select_package_coupon_list('6');
		
		$data;
		
		foreach ($member_coupon as $mc) {
			for ($i=0; $i < count($current_package_coupon); $i++) {
				$cpc = $current_package_coupon[$i];
				$upc = $upgrade_package_coupon[$i];
				if ($mc['service_group_id'] == $cpc['service_group_id']) {
					$data[$i]['service_group_id'] = $mc['service_group_id'];
					$data[$i]['qty'] = $upc['qty']-($cpc['qty']-$mc['qty']);
					break;
				}
			}
		}
		
		log_message('debug',"## result coupon=".print_r($data, TRUE));

		$this->db->set('package_cd', '6');
		$this->db->set('update_dt' , 'NOW()', FALSE);
		$this->db->where('idx', $idx);
		$this->db->update('tb_member_package');

		foreach ($data as $item) {
			$this->db->set('qty', $item['qty']);
			$this->db->where('idx', $idx);
			$this->db->where('service_group_id',$item['service_group_id']);
			$this->db->update('tb_member_package_coupon');
		}
		
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			log_message('error','## DB업데이트중 에러 발생');
			echo "<pre>";
			print_r($this->db->queries);
			echo "</pre>";
			die('에러 발생');
		}
		return TRUE;
		
	}

	/**
	 * 쿠폰 리스트
	 */
	function select_package_coupon_list($package_cd) {
		log_message('debug',"#### Package_model::select_package_coupon_list($package_cd)");

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       PC.* ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = PC.package_cd) package_name ";
		$sql .= PHP_EOL."     , (SELECT service_group_name FROM tb_service_group WHERE service_group_id = PC.service_group_id) service_group_name ";
		$sql .= PHP_EOL." FROM  tb_package_coupon PC ";
		$sql .= PHP_EOL." WHERE package_cd = ? ";
		$query = $this->db->query($sql, array($package_cd)) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

}

/* End of file member_model.php */
/* Location: ./application/models/member/member_model.php */